--    Program: 		CAPARIMP_INVOICE_LINE.sql
--    Description:  Import AR Invoice Line information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARD1 DISABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARU1 DISABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_BRI1 DISABLE;
ALTER TRIGGER RA_CUSTOMER_TRX_BRI DISABLE;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
	lv_FileName		VARCHAR2(100)	:=	'TEST.log';
	lv_PathName		VARCHAR2(100)	:=	'/tmp';
	lv_UTL_File		UTL_FILE.File_Type;
	lv_counter		VARCHAR(100);
   
   l_customer_trx_line_id	number;
                      		                 		
   cursor cur_invoice is
      select * from CRST_RA_CUSTOMER_TRX_LINES_ALL
      --WHERE CUSTOMER_TRX_LINE_ID = 47582
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   lv_counter := 0;
   
   for cur in cur_invoice loop   	
     l_err_msg_tmp := '';
     
     lv_counter := lv_counter + 1;
     
     lv_UTL_File := UTL_FILE.FOpen(lv_PathName, lv_FileName, 'w');
     UTL_FILE.PUT_LINE(lv_UTL_File, lv_counter);
	 UTL_FILE.FClose(lv_UTL_File);
     
     select RA_CUSTOMER_TRX_S.NEXTVAL
     into   l_customer_trx_line_id
     from   dual;
     
     update CRST_RA_CUSTOMER_TRX_LINES_ALL
     set    customer_trx_line_id_n = l_customer_trx_line_id
     where  customer_trx_line_id = cur.customer_trx_line_id;
     
     begin
       -- import data
       insert into RA_CUSTOMER_TRX_LINES_ALL 
       (
         CUSTOMER_TRX_LINE_ID            ,
         LAST_UPDATE_DATE                ,
         LAST_UPDATED_BY                 ,
         CREATION_DATE                   ,
         CREATED_BY                      ,
         LAST_UPDATE_LOGIN               ,
         CUSTOMER_TRX_ID                 ,
         LINE_NUMBER                     ,
         SET_OF_BOOKS_ID                 ,
         REASON_CODE                     ,
         INVENTORY_ITEM_ID               ,
         DESCRIPTION                     ,
         PREVIOUS_CUSTOMER_TRX_ID        ,
         PREVIOUS_CUSTOMER_TRX_LINE_ID   ,
         QUANTITY_ORDERED                ,
         QUANTITY_CREDITED               ,
         QUANTITY_INVOICED               ,
         UNIT_STANDARD_PRICE             ,
         UNIT_SELLING_PRICE              ,
         SALES_ORDER                     ,
         SALES_ORDER_REVISION            ,
         SALES_ORDER_LINE                ,
         SALES_ORDER_DATE                ,
         ACCOUNTING_RULE_ID              ,
         ACCOUNTING_RULE_DURATION        ,
         LINE_TYPE                       ,
         ATTRIBUTE_CATEGORY              ,
         ATTRIBUTE1                      ,
         ATTRIBUTE2                      ,
         ATTRIBUTE3                      ,
         ATTRIBUTE4                      ,
         ATTRIBUTE5                      ,
         ATTRIBUTE6                      ,
         ATTRIBUTE7                      ,
         ATTRIBUTE8                      ,
         ATTRIBUTE9                      ,
         ATTRIBUTE10                     ,
         REQUEST_ID                      ,
         PROGRAM_APPLICATION_ID          ,
         PROGRAM_ID                      ,
         PROGRAM_UPDATE_DATE             ,
         RULE_START_DATE                 ,
         INITIAL_CUSTOMER_TRX_LINE_ID    ,
         INTERFACE_LINE_CONTEXT          ,
         INTERFACE_LINE_ATTRIBUTE1       ,
         INTERFACE_LINE_ATTRIBUTE2       ,
         INTERFACE_LINE_ATTRIBUTE3       ,
         INTERFACE_LINE_ATTRIBUTE4       ,
         INTERFACE_LINE_ATTRIBUTE5       ,
         INTERFACE_LINE_ATTRIBUTE6       ,
         INTERFACE_LINE_ATTRIBUTE7       ,
         INTERFACE_LINE_ATTRIBUTE8       ,
         SALES_ORDER_SOURCE              ,
         TAXABLE_FLAG                    ,
         EXTENDED_AMOUNT                 ,
         REVENUE_AMOUNT                  ,
         AUTORULE_COMPLETE_FLAG          ,
         LINK_TO_CUST_TRX_LINE_ID        ,
         ATTRIBUTE11                     ,
         ATTRIBUTE12                     ,
         ATTRIBUTE13                     ,
         ATTRIBUTE14                     ,
         ATTRIBUTE15                     ,
         TAX_PRECEDENCE                  ,
         TAX_RATE                        ,
         ITEM_EXCEPTION_RATE_ID          ,
         TAX_EXEMPTION_ID                ,
         MEMO_LINE_ID                    ,
         AUTORULE_DURATION_PROCESSED     ,
         UOM_CODE                        ,
         DEFAULT_USSGL_TRANSACTION_CODE  ,
         DEFAULT_USSGL_TRX_CODE_CONTEXT  ,
         INTERFACE_LINE_ATTRIBUTE10      ,
         INTERFACE_LINE_ATTRIBUTE11      ,
         INTERFACE_LINE_ATTRIBUTE12      ,
         INTERFACE_LINE_ATTRIBUTE13      ,
         INTERFACE_LINE_ATTRIBUTE14      ,
         INTERFACE_LINE_ATTRIBUTE15      ,
         INTERFACE_LINE_ATTRIBUTE9       ,
         VAT_TAX_ID                      ,
         AUTOTAX                         ,
         LAST_PERIOD_TO_CREDIT           ,
         ITEM_CONTEXT                    ,
         TAX_EXEMPT_FLAG                 ,
         TAX_EXEMPT_NUMBER               ,
         TAX_EXEMPT_REASON_CODE          ,
         TAX_VENDOR_RETURN_CODE          ,
         SALES_TAX_ID                    ,
         LOCATION_SEGMENT_ID             ,
         MOVEMENT_ID                     ,
         ORG_ID                          ,
         WH_UPDATE_DATE                  ,
         GLOBAL_ATTRIBUTE1               ,
         GLOBAL_ATTRIBUTE2               ,
         GLOBAL_ATTRIBUTE3               ,
         GLOBAL_ATTRIBUTE4               ,
         GLOBAL_ATTRIBUTE5               ,
         GLOBAL_ATTRIBUTE6               ,
         GLOBAL_ATTRIBUTE7               ,
         GLOBAL_ATTRIBUTE8               ,
         GLOBAL_ATTRIBUTE9               ,
         GLOBAL_ATTRIBUTE10              ,
         GLOBAL_ATTRIBUTE11              ,
         GLOBAL_ATTRIBUTE12              ,
         GLOBAL_ATTRIBUTE13              ,
         GLOBAL_ATTRIBUTE14              ,
         GLOBAL_ATTRIBUTE15              ,
         GLOBAL_ATTRIBUTE16              ,
         GLOBAL_ATTRIBUTE17              ,
         GLOBAL_ATTRIBUTE18              ,
         GLOBAL_ATTRIBUTE19              ,
         GLOBAL_ATTRIBUTE20              ,
         GLOBAL_ATTRIBUTE_CATEGORY       ,
         GROSS_UNIT_SELLING_PRICE        ,
         GROSS_EXTENDED_AMOUNT           ,
         AMOUNT_INCLUDES_TAX_FLAG        ,
         TAXABLE_AMOUNT                  ,
         WAREHOUSE_ID                    ,
         TRANSLATED_DESCRIPTION          ,
         EXTENDED_ACCTD_AMOUNT           ,
         BR_REF_CUSTOMER_TRX_ID          ,
         BR_REF_PAYMENT_SCHEDULE_ID      ,
         BR_ADJUSTMENT_ID                ,
         MRC_EXTENDED_ACCTD_AMOUNT       ,
         PAYMENT_SET_ID                  ,
         CONTRACT_LINE_ID                ,
         SOURCE_DATA_KEY1                ,
         SOURCE_DATA_KEY2                ,
         SOURCE_DATA_KEY3                ,
         SOURCE_DATA_KEY4                ,
         SOURCE_DATA_KEY5                ,
         INVOICED_LINE_ACCTG_LEVEL
       )
       values
       (       
         l_customer_trx_line_id              ,
         cur.LAST_UPDATE_DATE                ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.CREATION_DATE                   ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.LAST_UPDATE_LOGIN               ,
         CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.CUSTOMER_TRX_ID),
         cur.LINE_NUMBER                     ,
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
         cur.REASON_CODE                     ,
         cur.INVENTORY_ITEM_ID	             , --NULL
         cur.DESCRIPTION                     ,
         CAPARIMP_MAPPING.GetCustomerTrxIDMapping(cur.PREVIOUS_CUSTOMER_TRX_ID),
         cur.PREVIOUS_CUSTOMER_TRX_LINE_ID   ,
         cur.QUANTITY_ORDERED                ,
         cur.QUANTITY_CREDITED               ,
         cur.QUANTITY_INVOICED               ,
         cur.UNIT_STANDARD_PRICE             ,
         cur.UNIT_SELLING_PRICE              ,
         cur.SALES_ORDER                     ,
         cur.SALES_ORDER_REVISION            ,
         cur.SALES_ORDER_LINE                ,
         cur.SALES_ORDER_DATE                ,
         cur.ACCOUNTING_RULE_ID              ,
         cur.ACCOUNTING_RULE_DURATION        ,
         cur.LINE_TYPE                       ,
         cur.ATTRIBUTE_CATEGORY              ,
         cur.ATTRIBUTE1                      ,
         cur.ATTRIBUTE2                      ,
         cur.ATTRIBUTE3                      ,
         cur.ATTRIBUTE4                      ,
         cur.ATTRIBUTE5                      ,
         cur.ATTRIBUTE6                      ,
         cur.ATTRIBUTE7                      ,
         cur.ATTRIBUTE8                      ,
         cur.ATTRIBUTE9                      ,
         cur.ATTRIBUTE10                     ,
         cur.REQUEST_ID                      ,
         cur.PROGRAM_APPLICATION_ID          ,
         cur.PROGRAM_ID                      ,
         cur.PROGRAM_UPDATE_DATE             ,
         cur.RULE_START_DATE                 ,
         cur.INITIAL_CUSTOMER_TRX_LINE_ID    ,
         cur.INTERFACE_LINE_CONTEXT          ,
         cur.INTERFACE_LINE_ATTRIBUTE1       ,
         cur.INTERFACE_LINE_ATTRIBUTE2       ,
         cur.INTERFACE_LINE_ATTRIBUTE3       ,
         cur.INTERFACE_LINE_ATTRIBUTE4       ,
         cur.INTERFACE_LINE_ATTRIBUTE5       ,
         cur.INTERFACE_LINE_ATTRIBUTE6       ,
         cur.INTERFACE_LINE_ATTRIBUTE7       ,
         cur.INTERFACE_LINE_ATTRIBUTE8       ,
         cur.SALES_ORDER_SOURCE              ,
         cur.TAXABLE_FLAG                    ,
         cur.EXTENDED_AMOUNT                 ,
         cur.REVENUE_AMOUNT                  ,
         cur.AUTORULE_COMPLETE_FLAG          ,
         cur.LINK_TO_CUST_TRX_LINE_ID        ,
         cur.ATTRIBUTE11                     ,
         cur.ATTRIBUTE12                     ,
         cur.ATTRIBUTE13                     ,
         cur.ATTRIBUTE14                     ,
         cur.ATTRIBUTE15                     ,
         cur.TAX_PRECEDENCE                  ,
         cur.TAX_RATE                        ,
         cur.ITEM_EXCEPTION_RATE_ID          ,
         cur.TAX_EXEMPTION_ID                ,
         CAPARIMP_MAPPING.GetMemoLineIDMapping(cur.ORG_ID, cur.MEMO_LINE_ID),
         cur.AUTORULE_DURATION_PROCESSED     ,
         cur.UOM_CODE                        ,
         cur.DEFAULT_USSGL_TRANSACTION_CODE  ,
         cur.DEFAULT_USSGL_TRX_CODE_CONTEXT  ,
         cur.INTERFACE_LINE_ATTRIBUTE10      ,
         cur.INTERFACE_LINE_ATTRIBUTE11      ,
         cur.INTERFACE_LINE_ATTRIBUTE12      ,
         cur.INTERFACE_LINE_ATTRIBUTE13      ,
         cur.INTERFACE_LINE_ATTRIBUTE14      ,
         cur.INTERFACE_LINE_ATTRIBUTE15      ,
         cur.INTERFACE_LINE_ATTRIBUTE9       ,
         cur.VAT_TAX_ID                      ,
         cur.AUTOTAX                         ,
         cur.LAST_PERIOD_TO_CREDIT           ,
         cur.ITEM_CONTEXT                    ,
         cur.TAX_EXEMPT_FLAG                 ,
         cur.TAX_EXEMPT_NUMBER               ,
         cur.TAX_EXEMPT_REASON_CODE          ,
         cur.TAX_VENDOR_RETURN_CODE          ,
         cur.SALES_TAX_ID                    ,
         cur.LOCATION_SEGMENT_ID             ,
         cur.MOVEMENT_ID                     ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.WH_UPDATE_DATE                  ,
         cur.GLOBAL_ATTRIBUTE1               ,
         cur.GLOBAL_ATTRIBUTE2               ,
         cur.GLOBAL_ATTRIBUTE3               ,
         cur.GLOBAL_ATTRIBUTE4               ,
         cur.GLOBAL_ATTRIBUTE5               ,
         cur.GLOBAL_ATTRIBUTE6               ,
         cur.GLOBAL_ATTRIBUTE7               ,
         cur.GLOBAL_ATTRIBUTE8               ,
         cur.GLOBAL_ATTRIBUTE9               ,
         cur.GLOBAL_ATTRIBUTE10              ,
         cur.GLOBAL_ATTRIBUTE11              ,
         cur.GLOBAL_ATTRIBUTE12              ,
         cur.GLOBAL_ATTRIBUTE13              ,
         cur.GLOBAL_ATTRIBUTE14              ,
         cur.GLOBAL_ATTRIBUTE15              ,
         cur.GLOBAL_ATTRIBUTE16              ,
         cur.GLOBAL_ATTRIBUTE17              ,
         cur.GLOBAL_ATTRIBUTE18              ,
         cur.GLOBAL_ATTRIBUTE19              ,
         cur.GLOBAL_ATTRIBUTE20              ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
         cur.GROSS_UNIT_SELLING_PRICE        ,
         cur.GROSS_EXTENDED_AMOUNT           ,
         cur.AMOUNT_INCLUDES_TAX_FLAG        ,
         cur.TAXABLE_AMOUNT                  ,
         cur.WAREHOUSE_ID                    ,
         cur.TRANSLATED_DESCRIPTION          ,
         cur.EXTENDED_ACCTD_AMOUNT           ,
         cur.BR_REF_CUSTOMER_TRX_ID          ,
         cur.BR_REF_PAYMENT_SCHEDULE_ID      ,
         cur.BR_ADJUSTMENT_ID                ,
         cur.MRC_EXTENDED_ACCTD_AMOUNT       ,
         cur.PAYMENT_SET_ID                  ,
         cur.CONTRACT_LINE_ID                ,
         cur.SOURCE_DATA_KEY1                ,
         cur.SOURCE_DATA_KEY2                ,
         cur.SOURCE_DATA_KEY3                ,
         cur.SOURCE_DATA_KEY4                ,
         cur.SOURCE_DATA_KEY5                ,
         cur.INVOICED_LINE_ACCTG_LEVEL
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_INVOICE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/

ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARD1 ENABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_ARU1 ENABLE;
ALTER TRIGGER AX_RA_CUSTOMER_TRX_BRI1 ENABLE;
ALTER TRIGGER RA_CUSTOMER_TRX_BRI ENABLE;

--exit